package com.tnsw.coreutils.database;

import java.io.File;
import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.util.ArrayList;

import com.tnsw.coreutils.AsyncManager;
import com.tnsw.coreutils.MyLog;
import com.tnsw.coreutils.files.FileUtils;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Environment;
import android.util.Base64;
import android.util.Log;
import android.widget.Toast;

public class DBManagerBase extends SQLiteOpenHelper {
	
	public static String DB_BASE_PATH = Environment.getExternalStorageDirectory().getAbsolutePath() + "/";
	public static String DB_DIRECTORY = "";
	public static String DB_FILE = "";
	public static boolean REPLACED_FROM_ASSET = false;
	private String baseDir = DB_BASE_PATH + DB_DIRECTORY + "/";
	private String fullDBPath = baseDir + DB_FILE;
	
	protected String tag = "DBManagerBase";
	protected Context context;
	
	private ArrayList<DBActionListener> listeners;
	
	public DBManagerBase(Context context) {
		super(context, "", null, 1);
		this.context = context;
		
		this.initialize();
		int version = this.checkVersion();
		MyLog.d(tag, "This DB version=" + version);
		
		int primarykey = this.getNewPrimarykey();
		MyLog.d(tag, "primarykey=" + primarykey);
		
		this.listeners = new ArrayList<DBActionListener>();
				
		//String currentDBFile = this.context.getDatabasePath(DB_FILE).getAbsolutePath();
		//Log.d(tag, "currentDBFile=" + currentDBFile);		
				
	}
	
	public void registerActionListener(DBActionListener listener) {
		if (this.listeners.contains(listener) == false) {
			this.listeners.add(listener);
			MyLog.d(tag, "registeredAction: " + listener.getClass().getName());
		}
	}
	
	public void unregisterActionListener(DBActionListener listener) {
		if (this.listeners.contains(listener)) {
			this.listeners.remove(listener);
			MyLog.d(tag, "unregisteredAction: " + listener.getClass().getName());
		}
	
	}
	
	public ArrayList<DBActionListener> getListeners() {
		return(this.listeners);
	}
	
	private void broadcastAction(String action, String tableName, int primaryKey, RecordBase record) {
		try {
			for (DBActionListener listener : this.listeners) {
				if (listener != null) {
					listener.onRecordAffected(action, tableName, primaryKey, record);
				}
			}
		} catch (Exception e) {
			MyLog.e(tag, "broadcastAction - " + e.toString(), e);
		}
	}
	
	private void initialize() {
		if ( (DB_BASE_PATH.equals("")) || (DB_FILE.equals("")) ) {
			Log.e(tag, "DB_BASE_PATH='" + DB_BASE_PATH + "' or DB_FILE='" + DB_FILE + "' is still empty, set this value first");
			return;
		}
		
		MyLog.d(tag, "Initialized..");
		
		this.baseDir = DB_BASE_PATH + DB_DIRECTORY + "/";
		this.fullDBPath = baseDir + DB_FILE;
	
		boolean directoryOK = false;
		
		MyLog.d(tag, "baseDir=" + baseDir);
		MyLog.d(tag, "fullDBPath=" + fullDBPath);
		
		File dir = new File(baseDir);
		if (dir.exists() == false) {
			MyLog.d(tag, "Not found directory for DB: " + DB_BASE_PATH + DB_DIRECTORY + ", going to create");
			
			try {
				boolean b = dir.mkdir();
				if (b == true) {
					Log.d(tag, "created directory for DB: " + baseDir);
					directoryOK = true;
				} else {
					Log.e(tag, "failed to create directory for DB: " + baseDir);
				}
					
			} catch (Exception e) {
				Log.e(tag, "Exception on creating directory '" + baseDir + "'", e);
			}
			
			
		} else {  // baseDir exists, now working for db file
			directoryOK = true;
			
		}
		
		MyLog.d(tag, "directoryOK=" + directoryOK);
			
		if (directoryOK == true) {
			File f = new File(fullDBPath);
			if (f.exists() == false) {
				Log.d(tag, "Not found existing DB: " + fullDBPath);
				try {					
					FileUtils.copyFile(this.context.getAssets().open(DB_FILE), fullDBPath);
					MyLog.d(tag, "Copied from asset to " + fullDBPath + " successfully");
				} catch (Exception e) {
					MyLog.e(tag, "Error copying from asset to '" + fullDBPath + "'", e);
				}
				
			} else { // DB file exists, choose whether to overwrite?
				MyLog.d(tag, "REPLACE_FROM_ASSET=" + REPLACED_FROM_ASSET);
				if (REPLACED_FROM_ASSET) {
					try {
						File deleteFile = new File(fullDBPath);
						deleteFile.delete();
						MyLog.d(tag, "Deleted " + fullDBPath);
					
						FileUtils.copyFile(this.context.getAssets().open(DB_FILE), fullDBPath);
						Log.d(tag, "Copied successfully from asset to " + fullDBPath);
					} catch (Exception e) {
						MyLog.e(tag, "Error replace from asset to '" + fullDBPath + "'", e);
					}
				} else {
					MyLog.d(tag, "Not replace db from asset");
				}
			}
			
			
		} else {
			MyLog.e(tag, "directoryOK==false, cannot create/connect database due to directory problem");
		}
	}
	
	public String getFullDatabasePath() {
		return(this.fullDBPath);
	}
	
	public int getNewPrimarykey() {
		int primarykey = 0;
		SQLiteDatabase db = null;
		
		try {
			db = this.getWritableDatabase();
			//db.beginTransactionNonExclusive();
			db.execSQL("update dbinfo set id=id+1");
			
			Cursor cursor = db.rawQuery("select id from dbinfo", null);
			if (cursor.getCount() != 0) {
				cursor.moveToFirst();
				primarykey = cursor.getInt(0);
			}
			cursor.close();
			
			
			//db.setTransactionSuccessful();
			//db.endTransaction();
			//Log.d(tag, "committed");
			
		} catch (Exception e) {
			Log.e(tag, "getNewPrimarykey() - " + e.toString(), e);
			if (db != null) {
				if (db.inTransaction() == true) {
					//db.endTransaction();
				}	
			}
			
		}
		
		if (db != null) {
			db.close();			
		}
		
		return(primarykey);
	}
	
	private int checkVersion() {
		int version = -1;
		SQLiteDatabase db = null;
		
		try {
			db = this.getWritableDatabase();
			
			Cursor cursor = db.rawQuery("select * from dbinfo", null);
			if (cursor.getCount() != 0) {
				cursor.moveToFirst();
				version = cursor.getInt(0);
			}
			cursor.close();
		} catch (Exception e) {
			MyLog.e(tag, "checkVersion() - " + e.toString(), e);
		}
		
		if (db != null) {
			db.close();
		}
		
		return(version);
	}
	


	@Override
	public void onCreate(SQLiteDatabase db) {
		MyLog.d(tag, "onCreate()");
	}
	
	@Override
	public void onOpen(SQLiteDatabase db) {
		super.onOpen(db);
		MyLog.d(tag, "onOpen()");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		MyLog.d(tag, "onUpgrade(oldVersion=" + oldVersion + ", newVersion=" + newVersion);
	}
	
	@Override
	public SQLiteDatabase getReadableDatabase()
	{
	    SQLiteDatabase database = SQLiteDatabase.openDatabase(this.fullDBPath, null, SQLiteDatabase.OPEN_READONLY);
	    return database;
	}

	@Override
	public SQLiteDatabase getWritableDatabase()
	{
	    SQLiteDatabase database = SQLiteDatabase.openDatabase(this.fullDBPath, null, SQLiteDatabase.OPEN_READWRITE);
	    return database;
	}
	
	public void uploadDB4Debug(AsyncManager asyncM, String destFileName) {
		MyLog.d(tag, "uploading db for debuging");
		try {
			byte[] b = FileUtils.readFile(this.getFullDatabasePath());
			String base64 = Base64.encodeToString(b, Base64.DEFAULT);
			asyncM.appendBase64(base64, destFileName);	
			
		} catch (Exception e) {
			MyLog.e(tag, "updateDB4Debug() - " + e.toString(), e);
		}
		MyLog.d(tag, "upload debung into queue");
	}
	
	public ArrayList<RecordBase> getRecords(String recordClassName, String sql, String values[], int position) {
		SQLiteDatabase db = null;
		ArrayList<RecordBase> a = null;
		
		try {
			db = this.getReadableDatabase();
			Cursor cursor = db.rawQuery(sql, values);
			boolean b = cursor.moveToPosition(position);
			if (b == true) {
				a = new ArrayList<RecordBase>();
				if (cursor.isAfterLast() == false) {
					
					while (!cursor.isAfterLast()) {
						Class c = Class.forName(recordClassName);
						RecordBase record = (RecordBase) c.getConstructors()[0].newInstance(null);
						
						for (String columnName : cursor.getColumnNames()) {
							
							//Field f = c.getDeclaredField(columnName);
							Field f = c.getField(columnName);
							int idx = cursor.getColumnIndex(columnName);
							int type = cursor.getType(idx);
							switch(type) {
							
								case Cursor.FIELD_TYPE_BLOB:
									MyLog.d(tag, "c('" + columnName + "') type is BLOB");
									byte[] bb = cursor.getBlob(idx);
									f.set(record, bb);
									break;
									
								case Cursor.FIELD_TYPE_FLOAT:
									MyLog.d(tag, "c('" + columnName + "') type is FLOAT");
									float fValue = cursor.getFloat(idx);
									f.set(record, fValue);
									break;
									
								case Cursor.FIELD_TYPE_INTEGER:
									MyLog.d(tag, "c('" + columnName + "') type is INTEGER");
									long longValue = cursor.getLong(idx);
									if (longValue < Short.MAX_VALUE) {
										f.set(record, (short) longValue);
									} else if (longValue < Integer.MAX_VALUE) {
										f.set(record, (int) longValue);									
									} else {
										f.set(record, longValue);	
									}
									
									break;
									
								case Cursor.FIELD_TYPE_NULL:
									MyLog.d(tag, "c('" + columnName + "') type is NULL");
									break;
									
								case Cursor.FIELD_TYPE_STRING:
									MyLog.d(tag, "c('" + columnName + "') type is STRING");
									String strValue = cursor.getString(idx);
									f.set(record, strValue);
									break;
							}
						}
						
						a.add(record);
						MyLog.d(tag, "added 1 record");
						cursor.moveToNext();
					}
					
				}	
			}
			
		} catch (Exception e) {
			MyLog.e(tag, "getRecords(" + recordClassName + ", sql=" + sql + ", position=" + position + ") - " + e.toString(), e);
			
		}
		
		if (db != null) {
			db.close();
		}
		
		return(a);
		
	}	
	
	public long insertRecord(String tableName, RecordBase record, String nullColumnHack) {
		SQLiteDatabase db = null;
		ContentValues values = new ContentValues();
		long rowId = -1l;
		
		try {
			db = this.getWritableDatabase();
			
			Class c = record.getClass();

			for (Field field : c.getFields()) {
				if (field.getModifiers()== Modifier.PUBLIC) {
					String value = null;
					if (field.get(record) != null) {
						value = field.get(record).toString();
						MyLog.d(tag, "field(name=" + field.getName() + ").get(record)=" + field.get(record).getClass().getName());
						
					} else {
						MyLog.d(tag, "field(name=" + field.getName() + ").get(record)=null");
					}
					
					
					if (field.getName().equals("created")) {
						values.put("created", System.currentTimeMillis());
						
					} else {
						if (value != null) {
							values.put(field.getName(), field.get(record).toString());	
						}
							
					}
						
				}
				
			}	
			
			rowId = db.insert(tableName, nullColumnHack, values);

			MyLog.d(tag, "inserted record on table=" + tableName);
			this.broadcastAction("insert", tableName, record.primarykey, record);
		} catch (Exception e) {
			MyLog.e(tag, "insertRecord(tablename=" + tableName + ") - " + e.toString(), e);
		}

		if (db != null) {
			if (db.isOpen() == true) {
				db.close();
			}
		}		
		
		return(rowId);
	}
	
	public boolean compareRecord(RecordBase r1, RecordBase r2) {
		// true means same, false means different
		boolean b = false;
		
		Class c1 = r1.getClass();
		Class c2 = r2.getClass();
		
		try {
			for (Field field1 : c1.getFields()) {
				if (field1.getModifiers() == Modifier.PUBLIC) {
					String fieldName = field1.getName();
					Field field2 = c2.getField(fieldName);
					
					if ( (field1.get(r1) == null) && (field2.get(r2) == null) ) {
						b = true;
						
					} else if (field1.get(r1).equals(field2.get(r2)) == false) {
						MyLog.d(tag, "compare(r1!=r2, fieldName=" + fieldName + ") \r\n r1=" + r1.toString() + "\r\n r2=" + r2.toString());
						MyLog.d(tag, "field1.get(r1)=" + field1.get(r1).toString());
						MyLog.d(tag, "field2.get(r2)=" + field2.get(r2).toString());
						return(false);
					}
				}
			}			
		} catch (Exception e) {
			MyLog.w(tag, "r1 <> r2", e);
			b = false;
		}

		
		return(b);
	}
	
	public int deleteByPrimaryKey(String tableName, int primarykey) {
		SQLiteDatabase db = null;
		int result = 0;
		
		try {
			db = this.getWritableDatabase();
			result = db.delete(tableName, "primarykey=?", new String[] {String.valueOf(primarykey)});
			if (result == 0) {
				primarykey = 0;
			}
			
			this.broadcastAction("delete", tableName, primarykey, null);
			
		} catch (Exception e) {
			MyLog.e(tag, "deleteByPrimarykey(tablename=" + tableName + ", primarykey=" + primarykey + ") - " + e.toString(), e);
		}
		
		if (db != null) {
			if (db.isOpen() == true) {
				db.close();
			}
		}		
		
		return(result);		
	}
	
	public int deleteTable(String tableName) {
		SQLiteDatabase db = null;
		int result = 0;
		
		try {
			db = this.getWritableDatabase();
			result = db.delete(tableName, "", null);
			this.broadcastAction("delete", tableName, 0, null);
		} catch (Exception e) {
			MyLog.e(tag, "delete(tablename=" + tableName + ") - " + e.toString(), e);
		}
		
		if (db != null) {
			if (db.isOpen() == true) {
				db.close();
			}
		}		
		
		return(result);
	}

	public int updateRecordByPrimarykey(String tableName, Object record) {
		SQLiteDatabase db = null;
		ContentValues values = new ContentValues();
		int primarykey = 0;
		int recordAffected = 0;
		
		try {
			db = this.getWritableDatabase();
			
			Class c = record.getClass();
			//String sql = "update " + tableName + " set ";
			int i = 0;
			for (Field field : c.getFields()) {
				if (field.getModifiers()== Modifier.PUBLIC) {
					Log.d(tag, "field.getName()=" + field.getName());
					if (field.get(record) == null) {
						Log.d(tag, "field(name=" + field.getName() + ").get(record)=null");
					} else {
						Log.d(tag, "field(name=" + field.getName() + ").get=" + field.get(record).getClass().getName());	
					}
					
					if (field.getName().equals("primarykey")) {						
						primarykey = Integer.valueOf(field.get(record).toString());
						
					} else if (field.getName().equals("lastupd")) {
						values.put("lastupd", System.currentTimeMillis());
						
					} else if (field.get(record) == null) {
						values.putNull(field.getName());
						
					} else {
						values.put(field.getName(), field.get(record).toString());	
					}
						
				}
				
			}	
			
			recordAffected = db.update(tableName, values, "primarykey=" + String.valueOf(primarykey), null);
			this.broadcastAction("update", tableName, primarykey, (RecordBase) record);
			MyLog.d(tag, "updated table=" + tableName + ", primarykey=" + String.valueOf(primarykey));
		} catch (Exception e) {
			MyLog.e(tag, "updateRecordByPrimarykey(tablename=" + tableName + ") - " + e.toString(), e);
		}

		if (db != null) {
			if (db.isOpen() == true) {
				db.close();
			}
		}
	
		return(recordAffected);
	}
	
}

